﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DBUtility;

namespace SQLServerDAL
{
    /// <summary>
    /// 产品信息管理
    /// </summary>
    public class ProductBase
    {
        private SqlParameter[] Parms;
        private SqlConnection Conn;

        public ProductBase()
        {
            Conn = new SqlConnection(SqlHelper.ConnectionStringLocalTransaction);
        }

        #region 产品信息添加
        /// <summary>
        /// 产品信息添加
        /// </summary>
        /// <param name="_productObj"></param>
        /// <returns></returns>
        public string CreateProduct(Model.ProductBase _productObj)
        {
            Parms = new SqlParameter[] {
					new SqlParameter("@CategoryId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@ProductName", SqlDbType.NVarChar,512),
					new SqlParameter("@ProductNumber", SqlDbType.NVarChar,512),
					new SqlParameter("@TitlePicture", SqlDbType.NVarChar,512),
					new SqlParameter("@MarketPrice", SqlDbType.Decimal),
					new SqlParameter("@Price", SqlDbType.Decimal),
					new SqlParameter("@Price1", SqlDbType.Decimal),
					new SqlParameter("@Price2", SqlDbType.Decimal),
                    new SqlParameter("@BrandName",SqlDbType.NVarChar,256),
                    new SqlParameter("@ModelName",SqlDbType.NVarChar,256),
					new SqlParameter("@StockAmount", SqlDbType.Int),
					new SqlParameter("@StockStatus", SqlDbType.SmallInt),
					new SqlParameter("@StatusCode", SqlDbType.Int),
                    new SqlParameter("@Description",SqlDbType.VarChar),
                    new SqlParameter("@CreatedOn",SqlDbType.DateTime),
					new SqlParameter("@CreatedBy", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@BrandId", SqlDbType.Int),
					new SqlParameter("@ProductTypeCode", SqlDbType.Int), 
					new SqlParameter("@StatusTitle", SqlDbType.VarChar, 256),
					new SqlParameter("@BriefDescription", SqlDbType.VarChar), 
					new SqlParameter("@WarrantyInfo", SqlDbType.VarChar),
					new SqlParameter("@Approvals", SqlDbType.VarChar,256),
					new SqlParameter("@Capable", SqlDbType.VarChar,256),
					new SqlParameter("@PowerStorageorNot", SqlDbType.VarChar,256),
					new SqlParameter("@ColorTones", SqlDbType.VarChar,256),
					new SqlParameter("@Power", SqlDbType.VarChar,256),
					new SqlParameter("@Voltage", SqlDbType.VarChar,256),
					new SqlParameter("@Dimmable", SqlDbType.VarChar,256),
					new SqlParameter("@Package", SqlDbType.VarChar,256),
					new SqlParameter("@Life", SqlDbType.VarChar,256),
					new SqlParameter("@LampHolder", SqlDbType.VarChar,256),
					new SqlParameter("@LightSourceChip", SqlDbType.VarChar,256),
					new SqlParameter("@Illuminant", SqlDbType.VarChar,256),
					new SqlParameter("@Remarks", SqlDbType.VarChar),
					new SqlParameter("@Material", SqlDbType.VarChar,512),
					new SqlParameter("@CorrelatedColorTemperature", SqlDbType.VarChar,512),
					new SqlParameter("@SupplyAbility", SqlDbType.VarChar,512),
					new SqlParameter("@TargetMarkets", SqlDbType.VarChar,512),
					new SqlParameter("@LED", SqlDbType.VarChar,512),
                    new SqlParameter("@ProductId",SqlDbType.UniqueIdentifier,16,"Output"),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };
            Parms[0].Value = new Guid(_productObj.CategoryId);
            Parms[1].Value = _productObj.ProductName;
            Parms[2].Value = _productObj.ProductNumber;
            Parms[3].Value = _productObj.TitlePicture;
            Parms[4].Value = _productObj.MarketPrice;
            Parms[5].Value = _productObj.Price;
            Parms[6].Value = _productObj.Price1;
            Parms[7].Value = _productObj.Price2;
            Parms[8].Value = _productObj.BrandName;
            Parms[9].Value = _productObj.ModelName;
            Parms[10].Value = _productObj.StockAmount;
            Parms[11].Value = _productObj.StockStatus;
            Parms[12].Value = _productObj.StatusCode;
            Parms[13].Value = _productObj.Description;
            Parms[14].Value = _productObj.CreatedOn;
            Parms[15].Value = new Guid(_productObj.CreatedBy);
            Parms[16].Value = _productObj.BrandId;
            Parms[17].Value = _productObj.ProductTypeCode;
            Parms[18].Value = _productObj.StatusTitle;
            Parms[19].Value = _productObj.BriefDescription;
            Parms[20].Value = _productObj.WarrantyInfo;
            Parms[21].Value = _productObj.Approvals;
            Parms[22].Value = _productObj.Capable;
            Parms[23].Value = _productObj.PowerStorageorNot;
            Parms[24].Value = _productObj.ColorTones;
            Parms[25].Value = _productObj.Power;
            Parms[26].Value = _productObj.Voltage;
            Parms[27].Value = _productObj.Dimmable;
            Parms[28].Value = _productObj.Package;
            Parms[29].Value = _productObj.Life;
            Parms[30].Value = _productObj.LampHolder;
            Parms[31].Value = _productObj.LightSourceChip;
            Parms[32].Value = _productObj.Illuminant;
            Parms[33].Value = _productObj.Remarks;
            Parms[34].Value = _productObj.Material;
            Parms[35].Value = _productObj.CorrelatedColorTemperature;
            Parms[36].Value = _productObj.SupplyAbility;
            Parms[37].Value = _productObj.TargetMarkets;
            Parms[38].Value = _productObj.LED;
            Parms[39].Direction = ParameterDirection.Output;
            Parms[40].Direction = ParameterDirection.ReturnValue;

            string result = "";
            int status = 0;
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "ProductBase_Create", Parms);
                status = ((Parms[40].Value != null) ? ((int)Parms[40].Value) : -1);
                if (status == 0)
                {
                    result = "succeeded";
                    if (Common.Utils.IsGuid(Parms[39].Value.ToString()))
                        _productObj.ProductId = Parms[39].Value.ToString();

                    result += "|" + _productObj.ProductId;
                }
                else
                {
                    result = status.ToString(); ///exists;
                }
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return result;
        }
        #endregion

        #region 产品信息修改
        /// <summary>
        /// 产品信息修改
        /// </summary>
        /// <param name="_productObj"></param>
        /// <returns></returns>
        public string ModifyProduct(Model.ProductBase _productObj)
        {
            Parms = new SqlParameter[] {
					new SqlParameter("@CategoryId", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@ProductName", SqlDbType.NVarChar,512),
					new SqlParameter("@ProductNumber", SqlDbType.NVarChar,512),
					new SqlParameter("@TitlePicture", SqlDbType.NVarChar,512),
					new SqlParameter("@MarketPrice", SqlDbType.Decimal),
					new SqlParameter("@Price", SqlDbType.Decimal),
					new SqlParameter("@Price1", SqlDbType.Decimal),
					new SqlParameter("@Price2", SqlDbType.Decimal),
                    new SqlParameter("@BrandName",SqlDbType.NVarChar,256),
                    new SqlParameter("@ModelName",SqlDbType.NVarChar,256),
					new SqlParameter("@StockAmount", SqlDbType.Int),
					new SqlParameter("@StockStatus", SqlDbType.SmallInt),
					new SqlParameter("@StatusCode", SqlDbType.SmallInt),
                    new SqlParameter("@Description",SqlDbType.VarChar),
                    new SqlParameter("@ModifiedOn",SqlDbType.DateTime),
					new SqlParameter("@ModifiedBy", SqlDbType.UniqueIdentifier,16),
					new SqlParameter("@BrandId", SqlDbType.Int),
					new SqlParameter("@ProductTypeCode", SqlDbType.Int), 
					new SqlParameter("@StatusTitle", SqlDbType.VarChar, 256),
					new SqlParameter("@BriefDescription", SqlDbType.VarChar), 
					new SqlParameter("@WarrantyInfo", SqlDbType.VarChar),
					new SqlParameter("@Approvals", SqlDbType.VarChar,256),
					new SqlParameter("@Capable", SqlDbType.VarChar,256),
					new SqlParameter("@PowerStorageorNot", SqlDbType.VarChar,256),
					new SqlParameter("@ColorTones", SqlDbType.VarChar,256),
					new SqlParameter("@Power", SqlDbType.VarChar,256),
					new SqlParameter("@Voltage", SqlDbType.VarChar,256),
					new SqlParameter("@Dimmable", SqlDbType.VarChar,256),
					new SqlParameter("@Package", SqlDbType.VarChar,256),
					new SqlParameter("@Life", SqlDbType.VarChar,256),
					new SqlParameter("@LampHolder", SqlDbType.VarChar,256),
					new SqlParameter("@LightSourceChip", SqlDbType.VarChar,256),
					new SqlParameter("@Illuminant", SqlDbType.VarChar,256),
					new SqlParameter("@Remarks", SqlDbType.VarChar),
					new SqlParameter("@Material", SqlDbType.VarChar,512),
					new SqlParameter("@CorrelatedColorTemperature", SqlDbType.VarChar,512),
					new SqlParameter("@SupplyAbility", SqlDbType.VarChar,512),
					new SqlParameter("@TargetMarkets", SqlDbType.VarChar,512),
					new SqlParameter("@LED", SqlDbType.VarChar,512),
                    new SqlParameter("@ProductId",SqlDbType.UniqueIdentifier),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
            };
            Parms[0].Value = new Guid(_productObj.CategoryId);
            Parms[1].Value = _productObj.ProductName;
            Parms[2].Value = _productObj.ProductNumber;
            Parms[3].Value = _productObj.TitlePicture;
            Parms[4].Value = _productObj.MarketPrice;
            Parms[5].Value = _productObj.Price;
            Parms[6].Value = _productObj.Price1;
            Parms[7].Value = _productObj.Price2;
            Parms[8].Value = _productObj.BrandName;
            Parms[9].Value = _productObj.ModelName;
            Parms[10].Value = _productObj.StockAmount;
            Parms[11].Value = _productObj.StockStatus;
            Parms[12].Value = _productObj.StatusCode;
            Parms[13].Value = _productObj.Description;
            Parms[14].Value = _productObj.ModifiedOn;
            Parms[15].Value = new Guid(_productObj.ModifiedBy);
            Parms[16].Value = _productObj.BrandId;
            Parms[17].Value = _productObj.ProductTypeCode;
            Parms[18].Value = _productObj.StatusTitle;
            Parms[19].Value = _productObj.BriefDescription;
            Parms[20].Value = _productObj.WarrantyInfo;
            Parms[21].Value = _productObj.Approvals;
            Parms[22].Value = _productObj.Capable;
            Parms[23].Value = _productObj.PowerStorageorNot;
            Parms[24].Value = _productObj.ColorTones;
            Parms[25].Value = _productObj.Power;
            Parms[26].Value = _productObj.Voltage;
            Parms[27].Value = _productObj.Dimmable;
            Parms[28].Value = _productObj.Package;
            Parms[29].Value = _productObj.Life;
            Parms[30].Value = _productObj.LampHolder;
            Parms[31].Value = _productObj.LightSourceChip;
            Parms[32].Value = _productObj.Illuminant;
            Parms[33].Value = _productObj.Remarks;
            Parms[34].Value = _productObj.Material;
            Parms[35].Value = _productObj.CorrelatedColorTemperature;
            Parms[36].Value = _productObj.SupplyAbility;
            Parms[37].Value = _productObj.TargetMarkets;
            Parms[38].Value = _productObj.LED;
            Parms[39].Value = new Guid(_productObj.ProductId);
            Parms[40].Direction = ParameterDirection.ReturnValue;

            string result = "";
            int status = 0;
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "ProductBase_Update", Parms);
                status = ((Parms[40].Value != null) ? ((int)Parms[40].Value) : -1);
                if (status == 0)
                {
                    result = "succeeded";
                    result += "|" + _productObj.ProductId;
                }
                else
                {
                    result = status.ToString(); ///exists;
                }
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return result;
        }
        #endregion

        #region 获取产品信息实体
        /// <summary>
        /// 获取产品信息实体
        /// </summary> 
        /// <returns></returns>
        public Model.ProductBase GetProductObjById(string productId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("SELECT * FROM vw_ProductBase WHERE ProductId = @ProductId AND DeletionStateCode=0");

            SqlParameter[] parameters = new SqlParameter[] { new SqlParameter("@ProductId", SqlDbType.UniqueIdentifier) };
            parameters[0].Value = new Guid(productId);

            Model.ProductBase _productObj = null;
            try
            {
                SqlDataReader rd = SqlHelper.ExecuteReader(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
                if (rd.Read())
                {
                    _productObj = new Model.ProductBase();
                    _productObj.ProductId = rd["ProductId"].ToString();
                    _productObj.CategoryId = rd["CategoryId"].ToString();
                    _productObj.CategoryNumber = rd["CategoryNumber"].ToString();
                    _productObj.CategoryName = rd["CategoryName"].ToString();
                    _productObj.ProductName = rd["ProductName"].ToString();
                    _productObj.ProductNumber = rd["ProductNumber"].ToString();
                    _productObj.TitlePicture = rd["TitlePicture"].ToString();
                    _productObj.MarketPrice = decimal.Parse(rd["MarketPrice"].ToString());
                    _productObj.Price = decimal.Parse(rd["Price"].ToString());
                    _productObj.Price1 = decimal.Parse(rd["Price1"].ToString());
                    _productObj.Price2 = decimal.Parse(rd["Price2"].ToString());
                    _productObj.BrandId = int.Parse(string.IsNullOrEmpty(rd["BrandId"].ToString()) ? "0" : rd["BrandId"].ToString());
                    _productObj.BrandName = rd["BrandName"].ToString();
                    _productObj.ModelName = rd["ModelName"].ToString();
                    _productObj.StockAmount = int.Parse(rd["StockAmount"].ToString());
                    _productObj.StockStatus = int.Parse(rd["StockStatus"].ToString());
                    _productObj.StatusCode = int.Parse(rd["StatusCode"].ToString());
                    _productObj.StatusTitle = rd["StatusTitle"].ToString();
                    _productObj.Description = rd["Description"].ToString();
                    _productObj.BriefDescription = rd["BriefDescription"].ToString();
                    _productObj.ModifiedOn = DateTime.Parse(rd["ModifiedOn"].ToString());
                    _productObj.CreatedOn = DateTime.Parse(rd["CreatedOn"].ToString());
                    _productObj.CreatedBy = rd["CreatedBy"].ToString();
                    _productObj.CreatedByName = rd["CreatedByName"].ToString();
                    _productObj.ModifiedBy = rd["ModifiedBy"].ToString();
                    _productObj.ModifiedByName = rd["ModifiedBy"].ToString();
                    _productObj.StockStatus = int.Parse(rd["ProductTypeCode"].ToString());
                    _productObj.WarrantyInfo = rd["WarrantyInfo"].ToString();
                    _productObj.Approvals = rd["Approvals"].ToString();
                    _productObj.Capable = rd["Capable"].ToString();
                    _productObj.PowerStorageorNot = rd["PowerStorageorNot"].ToString();
                    _productObj.ColorTones = rd["ColorTones"].ToString();
                    _productObj.Power = rd["Power"].ToString();
                    _productObj.Voltage = rd["Voltage"].ToString();
                    _productObj.Dimmable = rd["Dimmable"].ToString();
                    _productObj.Package = rd["Package"].ToString();
                    _productObj.Life = rd["Life"].ToString();
                    _productObj.LampHolder = rd["LampHolder"].ToString();
                    _productObj.LightSourceChip = rd["LightSourceChip"].ToString();
                    _productObj.Illuminant = rd["Illuminant"].ToString();
                    _productObj.Material = rd["Material"].ToString();
                    _productObj.CorrelatedColorTemperature = rd["CorrelatedColorTemperature"].ToString();
                    _productObj.SupplyAbility = rd["SupplyAbility"].ToString();
                    _productObj.TargetMarkets = rd["TargetMarkets"].ToString();
                    _productObj.LED = rd["LED"].ToString();
                    _productObj.ProductTypeCode = Convert.ToInt32(rd["ProductTypeCode"]);
                }
            }
            catch
            {
                return null;
            }
            return _productObj;
        }
        #endregion

        #region 设置产品状态
        /// <summary>
        /// 设置产品状态
        /// </summary>
        /// <param name="status"></param>
        /// <param name="productid"></param>
        /// <returns></returns>
        public string SetProductStatus(string status, string productid)
        {
            Parms = new SqlParameter[] { 
                new SqlParameter("@StatusCode",SqlDbType.Int),
                new SqlParameter("@ProductId",SqlDbType.UniqueIdentifier)
            };
            Parms[0].Value = Convert.ToInt16(status);
            Parms[1].Value = new Guid(productid);

            string res = "";
            try
            {
                res = SqlHelper.ExecuteNonQuery(Conn, CommandType.StoredProcedure, "ProductBase_UpdateStatusCode", Parms).ToString();
            }
            catch (Exception err)
            {
                res = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
            return res;

        }
        #endregion

        #region 修改商品价格数据
        /// <summary>
        /// 修改商品价格数据
        /// </summary>
        /// <param name="status"></param>
        /// <param name="productid"></param>
        /// <returns></returns>
        public void UpdatePrice(decimal marketprice, decimal price, string productid, out string result)
        {
            Parms = new SqlParameter[] { 
                new SqlParameter("@Price",SqlDbType.Decimal),
                new SqlParameter("@MarketPrice",SqlDbType.Decimal),
                new SqlParameter("@ProductId",SqlDbType.UniqueIdentifier)
            };
            Parms[0].Value = price;
            Parms[1].Value = marketprice;
            Parms[2].Value = new Guid(productid);

            result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, "UPDATE ProductBase SET MarketPrice = @MarketPrice, Price = @Price WHERE ProductId = @ProductId", Parms);
                result = "succeeded";
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }  
        }
        #endregion

        #region 修改商品库存设值
        /// <summary>
        /// 修改商品库存设值
        /// </summary>
        /// <param name="status"></param>
        /// <param name="productid"></param>
        /// <returns></returns>
        public void UpdateStockSettings(int stockamount, int status, string productid, out string result)
        {
            Parms = new SqlParameter[] { 
                new SqlParameter("@StockAmount",SqlDbType.Int),
                new SqlParameter("@StockStatus",SqlDbType.Int),
                new SqlParameter("@ProductId",SqlDbType.UniqueIdentifier)
            };
            Parms[0].Value = stockamount;
            Parms[1].Value = status;
            Parms[2].Value = new Guid(productid);

            result = "";
            try
            {
                SqlHelper.ExecuteNonQuery(Conn, CommandType.Text, "UPDATE ProductBase SET StockAmount = @StockAmount, StockStatus = @StockStatus WHERE ProductId = @ProductId", Parms);
                result = "succeeded";
            }
            catch (Exception err)
            {
                result = err.Message;
            }
            finally
            {
                Conn.Close();
                Conn.Dispose();
            }
        }
        #endregion

        #region 验证是否重复
        /// <summary>
        /// 验证是否重复
        /// </summary>
        /// <param name="categoryid">类别Id</param>
        /// <returns></returns>
        public bool IsExist(string name, string productId)
        {
            StringBuilder strSql = new StringBuilder();

            SqlParameter[] parameters = new SqlParameter[] { 
                new SqlParameter("@ProductId", SqlDbType.UniqueIdentifier, 16), 
                new SqlParameter("@ProductName", SqlDbType.VarChar, 256)
            };
            parameters[0].Value = new Guid(productId);
            parameters[1].Value = name;

            strSql.Append("SELECT COUNT(1) FROM ProductBase WHERE [ProductName]= @ProductName AND ProductId <> @ProductId");

            object _obj = SqlHelper.ExecuteScalar(SqlHelper.ConnectionStringLocalTransaction, CommandType.Text, strSql.ToString(), parameters);
            if (_obj != null)
            {
                if ((int)_obj > 0)
                    return true;
            }
            return false;
        }
        #endregion

        #region 删除产品信息
        /// <summary>
        /// 删除产品信息
        /// </summary>
        /// <param name="productid">产品Id</param>
        /// <param name="isLogical">是否逻辑删除(True 逻辑删除，False 物理删除)</param>
        public string Delete(string productid, bool isLogical)
        {
            string result = "";
            SqlParameter[] parameters = new SqlParameter[] {
					new SqlParameter("@ProductId", SqlDbType.UniqueIdentifier),
					new SqlParameter("@isLogical", SqlDbType.Bit),
                    new SqlParameter("@ReturnValue", SqlDbType.Int)
                };
            parameters[0].Value = new Guid(productid);
            parameters[1].Value = isLogical;
            parameters[2].Direction = ParameterDirection.ReturnValue;

            try
            {
                SqlHelper.ExecuteNonQuery(SqlHelper.ConnectionStringLocalTransaction, CommandType.StoredProcedure, "ProductBase_Delete", parameters);
                int status = ((parameters[2].Value != null) ? ((int)parameters[2].Value) : -1);
                switch (status)
                {
                    case 0:
                        result = "succeeded";
                        break; 
                    default:
                        result = status.ToString();
                        break;
                }
            }
            catch(Exception err)
            {
                result = err.Message;
            }

            return result;
        }
        #endregion
    }
}
